package com.seres.excelutils;

import com.seres.enums.ErrorEnum;
import com.seres.exception.ServerException;
import lombok.extern.slf4j.Slf4j;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Description : 导入
 * @ClassName : ExcelImport
 * @Author : 扶滨
 * @Date: 2021-01-18 11:20
 */
@Slf4j
@Component
public class ExcelImport<T> {

    private static final String uploadPath = "./";

    /**
     * @param file
     *            文件名(必须参数,完整路径，如："D:\\123.xls")
     * @param entityClass
     *            Bean.class(必须参数)
     * @param headerMap
     *            (必须参数)表头与字段对应关系(Map(表头，字段名))
     * @return list
     *
     * @TODO 数据从Excel表格导入到数据库
     */
    public List<T> importFromExcel(MultipartFile file, Class entityClass, Map<String, String> headerMap) {
        if(!file .isEmpty()){
            String fileName = file.getOriginalFilename();
            if (!fileName.endsWith(".xls")){
                throw new ServerException(ErrorEnum.ERROR_EXCLE_FORMAT);
            }
            BufferedOutputStream out = null;
            try{
                fileName = uploadPath + fileName;
                out = new BufferedOutputStream(new FileOutputStream(new File(fileName)));
                out.write(file.getBytes());
                out.flush();
                return impExcel(fileName, entityClass, headerMap);
            }catch(Exception e){
                e.printStackTrace();
            }finally {
                if(out!=null){
                    try {
                        out.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
        return null;
    }

    /**
     * @param fileName
     *            文件名(完整路径，如："D:\\123.xls")
     * @param entityClass
     *            Bean.class
     * @param headerMap
     *            表头与字段对应关系(Map(表头，字段名))
     * @return list
     */
    private static <T> List<T> impExcel(String fileName, Class<T> entityClass, Map<String, String> headerMap) {
        // 定义要返回的list
        List<T> resultList = new ArrayList<>();
        try {
            // 根据Excel数据源创建WorkBook
            Workbook workbook = Workbook.getWorkbook(new File(fileName));
            // 获取工作表
            Sheet sheet = workbook.getSheet(0);
            // 获取工作表的有效行数
            int realRows = 0;
            for (int i = 0; i < sheet.getRows(); i++) {
                int nullCols = 0;
                for (int j = 0; j < sheet.getColumns(); j++) {
                    Cell currentCell = sheet.getCell(j, i);
                    if (currentCell == null || "".equals(currentCell.getContents().toString())) {
                        nullCols++;
                    }
                }
                if (nullCols == sheet.getColumns()) {
                    break;
                } else {
                    realRows++;
                }
            }
            // 如果Excel中没有数据则提示错误
            if (realRows <= 1) {
                log.error("Excel文件中没有任何数据");
            }

            Cell[] firstRow = sheet.getRow(0);
            String[] excelFieldNames = new String[firstRow.length];
            // 获取Excel中的列名
            for (int i = 0; i < firstRow.length; i++) {
                excelFieldNames[i] = firstRow[i].getContents().toString().trim();
            }
            // 判断需要的字段在Excel中是否都存在
            boolean isExist = true;
            List<String> excelFieldList = Arrays.asList(excelFieldNames);
            for (String cnName : headerMap.keySet()) {
                if (!excelFieldList.contains(cnName)) {
                    isExist = false;
                    break;
                }
            }
            // 如果有列名不存在，则抛出异常，提示错误
            if (!isExist) {
                log.error("Excel中缺少必要的字段，或字段名称有误");
            }
            // 将列名和列号放入Map中,这样通过列名就可以拿到列号
            LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
            for (int i = 0; i < excelFieldNames.length; i++) {
                colMap.put(excelFieldNames[i], firstRow[i].getColumn());
            }
            // 将sheet转换为list
            for (int i = 1; i < realRows; i++) {
                // 新建要转换的对象
                T entity = entityClass.newInstance();
                // 给对象中的字段赋值
                for (Map.Entry<String, String> entry : headerMap.entrySet()) {
                    // 获取中文字段名
                    String cnNormalName = entry.getKey();
                    // 获取英文字段名
                    String enNormalName = entry.getValue();
                    // 根据中文字段名获取列号
                    int col = colMap.get(cnNormalName);
                    // 获取当前单元格中的内容
                    String content = sheet.getCell(col, i).getContents().toString().trim();
                    // 给对象赋值
                    setFieldValueByName(enNormalName, content, entity);
                }
                resultList.add(entity);
            }
            File file = new File(fileName);
            if(file.exists()){
                file.delete();
            }
            return resultList;
        } catch (BiffException e) {
            e.printStackTrace();
            return null;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        } catch (InstantiationException e) {
            e.printStackTrace();
            return null;
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

    }

    /**
     * @MethodName : setFieldValueByName
     * @Description : 根据字段名给对象的字段赋值
     * @param fieldName
     *            字段名
     * @param fieldValue
     *            字段值
     * @param o
     *            对象
     */
    private static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception {
        Field field = getFieldByName(fieldName, o.getClass());
        if (field != null && fieldValue != null) {
            field.setAccessible(true);
            // 获取字段类型
            Class<?> fieldType = field.getType();
            // 根据字段类型给字段赋值
            if (String.class == fieldType) {
                field.set(o, String.valueOf(fieldValue));
                return;
            }
            if(!fieldValue.toString().trim().isEmpty()){
                if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
                    field.set(o, Integer.parseInt(fieldValue.toString()));
                } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                    field.set(o, Long.valueOf(fieldValue.toString()));
                } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
                    field.set(o, Float.valueOf(fieldValue.toString()));
                } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
                    field.set(o, Short.valueOf(fieldValue.toString()));
                } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
                    field.set(o, Double.valueOf(fieldValue.toString()));
                } else if (Character.TYPE == fieldType) {
                    if ((fieldValue != null) && (fieldValue.toString().length() > 0)) {
                        field.set(o, Character.valueOf(fieldValue.toString().charAt(0)));
                    }
                } else if (Date.class == fieldType) {
                    try{
                        field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));
                    }catch(Exception e){
                        field.set(o, new SimpleDateFormat("yyyy-MM-dd").parse(fieldValue.toString()));
                    }
                } else {
                    field.set(o, fieldValue);
                }
            }
        }
    }

    /**
     * @MethodName : getFieldByName
     * @Description : 根据字段名获取字段
     * @param fieldName
     *            字段名
     * @param clazz
     *            包含该字段的类
     * @return 字段
     */
    private static Field getFieldByName(String fieldName, Class<?> clazz) {
        // 拿到本类的所有字段
        Field[] selfFields = clazz.getDeclaredFields();
        // 如果本类中存在该字段，则返回
        for (Field field : selfFields) {
            if (field.getName().equals(fieldName)) {
                return field;
            }
        }
        // 否则，查看父类中是否存在此字段，如果有则返回
        Class<?> superClazz = clazz.getSuperclass();
        if (superClazz != null && superClazz != Object.class) {
            return getFieldByName(fieldName, superClazz);
        }
        // 如果本类和父类都没有，则返回空
        return null;
    }


}
